The present disclosure relates to materialized query tables (MQTs), and more specifically, to generating MQTs that share data from a base table.
Data can be stored in and accessed from a database. In various embodiments, a database contains a base table, which has rows of data that can be accessed using queries. A query can be one or more statements interconnected with logical operators. The base table can be queried to obtain a result (data) to the query. In certain embodiments, a base table can contain large amounts of data, and the time it takes to return a result to a query can be significant. Furthermore, in particular embodiments, the same data can be accessed multiple times. As a result, the same or similar queries can be used multiple times.
Materialized query tables (MQT) are defined based upon the result of a query. The data that is contained in an MQT can derived from one or more base tables on which the materialized query table definition is based. In many respects, an MQT is a kind of materialized view. Both views and MQTs are defined on the basis of a query. An MQT stores the query results as data in memory location separate from the underlying table(s). This allows the data stored in the MQT to be used instead of the data that is in the underlying tables. Materialized query tables can significantly improve the performance of queries, especially complex queries. If the optimizer determines that a query or part of a query could be resolved using an MQT, the query might be rewritten to take advantage of the MQT.